//
//  DBManager.m
//  Nicejinux.NET
//
//  Created by Nicejinux on 12. 3. 27..
//  Copyright (c) 2012년 Nicejinux.NET. All rights reserved.
//

#import "DBManager.h"
#import "DBSQLQueries.h"
#import <sys/xattr.h>


static  DBManager*    g_sharedInstance = nil;



@interface DBManager ()

@property (nonatomic, assign)   sqlite3     *database;

- (id)initWithDB;

@end



@implementation DBManager

@synthesize database;

+ (DBManager*)sharedInstance
{
    if (g_sharedInstance)
        return g_sharedInstance;
    
    @synchronized(self)
    {
        if (!g_sharedInstance)
        {
            g_sharedInstance = [[self alloc] initWithDB];
        }
    }
    
    return g_sharedInstance;
}


- (id)initWithDB
{
    self = [super init];
    if (self)
    {
        [self initDB];
    }
    
    return self;
}

// iCloud Backup Skip Code
// if Not Exist, Reject!!!
// Don't care ios version under 5.01
// for 5.01
- (BOOL)addSkipBackupAttributeToItemAtURL501:(NSURL *)URL
{
    assert([[NSFileManager defaultManager] fileExistsAtPath: [URL path]]);
    
    const char* filePath = [[URL path] fileSystemRepresentation];
    
    const char* attrName = "com.apple.MobileBackup";
    u_int8_t attrValue = 1;
    
    int result = setxattr(filePath, attrName, &attrValue, sizeof(attrValue), 0, 0);
    return result == 0;
}

// for 5.1 or Later
- (BOOL)addSkipBackupAttributeToItemAtURL510:(NSURL *)URL
{
    assert([[NSFileManager defaultManager] fileExistsAtPath: [URL path]]);
    
    NSError *error = nil;
    BOOL success = [URL setResourceValue: [NSNumber numberWithBool: YES]
                                  forKey: NSURLIsExcludedFromBackupKey error: &error];
    if(!success){
//        NSLog(@"Error excluding %@ from backup %@", [URL lastPathComponent], error);
    }
    return success;
}

- (NSString *)applicationDocumentsDirectory 
{
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSURL *pathURL= [NSURL fileURLWithPath:documentPath];
    float version = [[[UIDevice currentDevice] systemVersion] floatValue];

    if (version >= 5.01 && version < 5.1)    
    {
        [self addSkipBackupAttributeToItemAtURL501:pathURL];
        return documentPath;
    }
    else if (version >= 5.1)
    {
        [self addSkipBackupAttributeToItemAtURL510:pathURL];
        return documentPath;
    }
    else
        return documentPath;
    
}

- (void)initDB {
    NSString *documentdirectory = [self applicationDocumentsDirectory];
    NSString *dbFilePath = [documentdirectory stringByAppendingPathComponent:@"database.db"];
    
    int result = sqlite3_open([dbFilePath UTF8String], &database);
    if (result != SQLITE_OK) 
    {
        NSLog(@"failed to open image db , result code : %d\n%@", result, dbFilePath);
    }
    else 
    {
        NSArray *dbQueryList = [NSArray arrayWithObjects:
                                CREATE_DBInfo,
                                CREATE_FlowerInfoList,
                                CREATE_FavoriteList,
                                nil];
        for (NSString *createSQL in dbQueryList)
        {
            char *pszErrMsg = NULL;
            result = sqlite3_exec (database, [createSQL UTF8String], NULL, NULL, &pszErrMsg);
            if (result != SQLITE_OK) 
            {
                NSLog(@"failed to create table , result code : %d\n%@", result, createSQL);
            }
            else 
            {
//                NSLog(@"success to create table\n%@", createSQL);
            }
        }
        
    }
}

- (void)dropDB
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentdirectory = [paths objectAtIndex:0];
    NSString *dbFilePath = [documentdirectory stringByAppendingPathComponent:@"database.db"];
    
    
    int result = sqlite3_open([dbFilePath UTF8String], &database);
    if (result != SQLITE_OK) 
    {
        NSLog(@"failed to open image db , result code : %d\n%@", result, dbFilePath);
    }
    else 
    {
        // 디비 버전을 업데이트 해야할 경우 디비 드롭하고 새로 만듬
        NSArray *dbQueryList = [NSArray arrayWithObjects:
                                DROP_FlowerInfoList,
                                DROP_FavoriteList,
                                nil];
        for (NSString *dropSQL in dbQueryList)
        {
            char *pszErrMsg = NULL;
            result = sqlite3_exec (database, [dropSQL UTF8String], NULL, NULL, &pszErrMsg);
            if (result != SQLITE_OK) 
            {
                NSLog(@"failed to drop table , result code : %d\n%@", result, dropSQL);
            }
            else 
            {
//                NSLog(@"success to drop table\n%@", dropSQL);
            }
        }
    }
}


- (int)columnTypeToInt: (NSString *) columnType {
	if ([columnType isEqualToString:@"INTEGER"]) {
		return SQLITE_INTEGER;
	}
	else if ([columnType isEqualToString:@"REAL"]) {
		return SQLITE_FLOAT;
	}
	else if ([columnType isEqualToString:@"TEXT"]) {
		return SQLITE_TEXT;
	}
	else if ([columnType isEqualToString:@"BLOB"]) {
		return SQLITE_BLOB;
	}
	else if ([columnType isEqualToString:@"NULL"]) {
		return SQLITE_NULL;
	}
	
	return SQLITE_TEXT;
}

- (int)typeForStatement: (sqlite3_stmt *) statement column: (int) column {
	const char * columnType = sqlite3_column_decltype(statement, column);
	
	if (columnType != NULL) {
		return [self columnTypeToInt: [[NSString stringWithUTF8String: columnType] uppercaseString]];
	}
	return sqlite3_column_type(statement, column);
}

- (NSArray *)columnTypesForStatement: (sqlite3_stmt *) statement {
	int columnCount = sqlite3_column_count(statement);
	
	NSMutableArray *columnTypes = [NSMutableArray array];
	for(int idx = 0; idx < columnCount; idx++) {
		[columnTypes addObject:[NSNumber numberWithInt:[self typeForStatement:statement column:idx]]];
	}

	return columnTypes;
}

- (NSArray *)columnNamesForStatement: (sqlite3_stmt *) statement {
	int columnCount = sqlite3_column_count(statement);
	
	NSMutableArray *columnNames = [NSMutableArray array];
	for (int idx = 0; idx < columnCount; idx++) {
		[columnNames addObject:[NSString stringWithUTF8String:sqlite3_column_name(statement, idx)]];
	}
    
	return columnNames;
}

- (id)valueFromStatement: (sqlite3_stmt *) statement column: (int) column columnTypes: (NSArray *) columnTypes {	
	int columnType = [[columnTypes objectAtIndex:column] intValue];
	
	if (columnType == SQLITE_INTEGER) {
		return [NSNumber numberWithInt:sqlite3_column_int(statement, column)];
	}
	else if (columnType == SQLITE_FLOAT) {
		return [NSNumber numberWithDouble: sqlite3_column_double(statement, column)];
	}
	else if (columnType == SQLITE_TEXT) {
		const char *text = (const char *) sqlite3_column_text(statement, column);
		if (text != nil) {
			return [NSString stringWithUTF8String: text];
		}
		else {
			return nil;
		}
	}
	else if (columnType == SQLITE_BLOB) {
		return [NSData dataWithBytes:sqlite3_column_blob(statement, column) length:sqlite3_column_bytes(statement, column)];
	}
	else if (columnType == SQLITE_NULL) {
		return nil;
	}
	return nil;
}

- (void)copyValuesFromStatement: (sqlite3_stmt *) statement toRow: (id) row columnTypes: (NSArray *) columnTypes columnNames: (NSArray *) columnNames {
	int columnCount = sqlite3_column_count(statement);
	
	for (int idx = 0; idx < columnCount; idx++) {
		id value = [self valueFromStatement:statement column:idx columnTypes: columnTypes];
		
        if(value != nil) {
			[row setValue: value forKey: [columnNames objectAtIndex:idx]];
        }
	}
}


- (BOOL)executeQuery:(NSString*)SQLQuery
{
	char *szErrMsg = nil;
    int result = sqlite3_exec(database, [SQLQuery UTF8String], nil,nil,&szErrMsg);
	if (result != SQLITE_OK) 
    {
//		NSLog(@"%s Error %d , %s\n%@", __func__, result, szErrMsg, SQLQuery);
        if (szErrMsg)
        {
            sqlite3_free(szErrMsg);
        }
        
		return NO;
	}

	return YES;
}

- (int)executeCountQuery:(NSString*)SQLQuery
{
//    NSLog(@"%s %@", __func__, SQLQuery);
	NSMutableArray *resultArray = [NSMutableArray array];
	sqlite3_stmt *statement; 

    int result = sqlite3_prepare_v2(database, [SQLQuery UTF8String], -1, &statement, NULL);
	if (result == SQLITE_OK) 
    {
		BOOL needsToFetchColumnTypesAndNames = YES;
		NSArray *columnTypes = nil;
		NSArray *columnNames = nil;
		while (sqlite3_step(statement)== SQLITE_ROW) 
        {
			if (needsToFetchColumnTypesAndNames) 
            {
				columnTypes = [self columnTypesForStatement: statement];
				columnNames = [self columnNamesForStatement: statement];
				needsToFetchColumnTypesAndNames = NO;
			}
        
			NSMutableDictionary *row = [NSMutableDictionary dictionaryWithCapacity:0];
			[self copyValuesFromStatement: statement toRow: row columnTypes: columnTypes columnNames: columnNames];
			[resultArray addObject:row];
		}
	}
    else 
    {
        NSLog(@"%s failed to select query , result code : %d\n%@", __func__, result, SQLQuery);
    }
    
	sqlite3_finalize(statement);
	
	return [resultArray count];
}

- (NSMutableArray*)executeSelectQuery:(NSString*)SQLQuery
{
//    NSLog(@"%s %@", __func__, SQLQuery);
	NSMutableArray *resultArray = [NSMutableArray array];
	sqlite3_stmt *statement; 

    int result = sqlite3_prepare_v2(database, [SQLQuery UTF8String], -1, &statement, NULL);
	if (result == SQLITE_OK) 
    {
		BOOL needsToFetchColumnTypesAndNames = YES;
		NSArray *columnTypes = nil;
		NSArray *columnNames = nil;
		while (sqlite3_step(statement)== SQLITE_ROW) 
        {
			if (needsToFetchColumnTypesAndNames) 
            {
				columnTypes = [self columnTypesForStatement: statement];
				columnNames = [self columnNamesForStatement: statement];
				needsToFetchColumnTypesAndNames = NO;
			}
			
			NSMutableDictionary *row = [NSMutableDictionary dictionaryWithCapacity:0];
			[self copyValuesFromStatement: statement toRow: row columnTypes: columnTypes columnNames: columnNames];
			[resultArray addObject:row];
		}
	}
    else 
    {
        NSLog(@"%s failed to select query , result code : %d\n%@", __func__, result, SQLQuery);
    }
	sqlite3_finalize(statement);
	
	return resultArray;
}

- (sqlite3*)getDatabaseObject
{
    return database;
}

@end
